/*
 * Esta classe foi criada com o objetivo de facilitar   
 *  a implementação de qualquer interação com o banco de dados.
 */
package br.com.niceshot;

import static java.lang.System.out;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.Statement;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.Map;

/**
 *
 * @author Alexandre
 */
public class PresetQueries extends DataToJson {

    protected Connection con = null;
    protected Statement stat = null;
    protected ResultSet rs = null;

    // QUERIES DE CONSULTA 
    // ******************************************************************************************************
    // Comando Select Simples
    public ArrayList<Object> SelectQuery(String query) {
        Map<String, String> resultSet = new HashMap<String, String>();

        ArrayList<Object> objResultSet = new ArrayList<>();

        try {
            con = Conexao.getConnection();
            stat = con.createStatement();
            rs = stat.executeQuery(query);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();

            while (rs.next()) {
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    resultSet.put(rsmd.getColumnName(i), rs.getString(i));
                }
                if (resultSet.size() > 0) {
                    objResultSet.add(novoHash(resultSet));
                }
                resultSet.clear();

            }
        } catch (Exception e) {
            out.println(e.getMessage());

        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
            try {
                stat.close();
            } catch (Exception e) {
            }
        }

        return objResultSet;

    }

    public String selectMaxTableId(String tabela, String nmCampo) {
        int maxId = 0;
        try {
            con = Conexao.getConnection();
            stat = con.createStatement();

            rs = stat.executeQuery("SELECT MAX(" + nmCampo + ") FROM " + tabela);
            rs.next();

            maxId = Integer.parseInt(rs.getString(1));

        } catch (Exception e) {
            return ("Error");

        } finally {

            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
            try {
                stat.close();
            } catch (Exception e) {
            }
        }
        return String.valueOf(maxId);
    }

    public Map<String, String> novoHash(Map<String, String> rowResultSet) {
        Map<String, String> newMap = new HashMap<String, String>(rowResultSet);
        return newMap;

    }

    // método para consulta que devolve uma tabela HTML com seus respectivos dados do ResultSet e os links  
    public String SelectTableResult(String nmServlet, String query, int regInicial, String[] headers) {
        String tableResult = "";

        Validators vld = new Validators();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            con = Conexao.getConnection();
            stat = con.createStatement();
            rs = stat.executeQuery(query);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();

            tableResult = "<div class=\"table-responsive\">";
            tableResult += "<table id=\"mytable\" class=\"table table-bordred table-striped\">";

            for (String header : headers) {
                tableResult += "<th>" + header + "</th>";
            }

            tableResult += "<th id='th_edit'>Editar</th><th id='th_remove'>Excluir</th>";

            while (rs.next()) {

                tableResult += "<tr>";
                for (int i = regInicial; i < (regInicial + headers.length); i++) {
                    if (vld.isValidDate(rs.getString(i))) {
                        sdf.applyPattern("yyyy-MM-dd"); // voltando ao padrão para converter para Date
                        Date dt;
                        dt = sdf.parse(rs.getString(i));
                        sdf.applyPattern("dd/MM/yyyy"); // Convertendo para mostrar na View
                        tableResult += "<td class='td_" + rsmd.getColumnName(i) + "' >" + sdf.format(dt) + "</td>";
                    } else {
                        tableResult += "<td class='td_" + rsmd.getColumnName(i) + "' >" + rs.getString(i) + "</td>";
                    }
                }
                tableResult += "<td class='td_edit'><p><button id='btnEditar' class=\"btn btn-primary btn-xs\" data-title=\"Edit\" onClick=\"edit('" + nmServlet + "'," + rs.getString(1) + ")\" data-placement=\"top\" rel=\"tooltip\"><span class=\"glyphicon glyphicon-pencil\"></span></button></p></td>";
                tableResult += "<td class='td_remove'><p><button class=\"btn btn-danger btn-xs\" data-title=\"Delete\" data-toggle=\"modal\" onClick=\"deletar('" + nmServlet + "'," + rs.getString(1) + ")\" data-target=\"#delete\" data-placement=\"top\" rel=\"tooltip\"><span class=\"glyphicon glyphicon-trash\"></span></button></p></td>";;
                tableResult += "</tr>";

            }
            tableResult += "</table>";
            tableResult += "</div>";

        } catch (Exception e) {
            out.println(e.getMessage());

        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
            try {
                stat.close();
            } catch (Exception e) {
            }
        }

        return tableResult;

    }

    public Map<String, String> SelectByID(String id, String tabela) {
        Map<String, String> resultSet = new HashMap<String, String>();
        Validators vld = new Validators();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            con = Conexao.getConnection();
            stat = con.createStatement();
            rs = stat.executeQuery("SELECT * FROM " + tabela);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();

            while (rs.next()) {
                if (rs.getString(1).equals(id)) {
                    for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                        if (vld.isValidDate(rs.getString(i))) {
                            sdf.applyPattern("yyyy-MM-dd"); // voltando ao padrão para converter para Date
                            Date dt;
                            dt = sdf.parse(rs.getString(i));
                            sdf.applyPattern("dd/MM/yyyy"); // Convertendo para mostrar na View
                            resultSet.put(rsmd.getColumnName(i), sdf.format(dt));
                        } else {
                            resultSet.put(rsmd.getColumnName(i), rs.getString(i));
                        }
                    }
                }

            }

        } catch (Exception e) {
            out.println(e.getMessage());

        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
            try {
                stat.close();
            } catch (Exception e) {
            }
        }

        return resultSet;

    }

    public ArrayList<Object> SelectAll(String tabela) {
        ArrayList<Object> resultSet = new ArrayList<Object>();
        ArrayList<String> idTabela = new ArrayList<String>();
        try {
            con = Conexao.getConnection();
            stat = con.createStatement();
            rs = stat.executeQuery("SELECT * FROM " + tabela);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();

            while (rs.next()) {
                idTabela.add(rs.getString(1));
            }

            for (int i = 0; i < idTabela.size(); i++) {
                resultSet.add(SelectByID(idTabela.get(i), tabela));
            }

        } catch (Exception e) {
            out.println(e.getMessage());

        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
            try {
                stat.close();
            } catch (Exception e) {
            }
        }

        return resultSet;

    }

    public ArrayList<Object> SelectAllById(String tabelaOrigem, String tabelaDestino, String id) {
        ArrayList<Object> resultSet = new ArrayList<Object>();
        ArrayList<String> idTabela = new ArrayList<String>();
        try {
            con = Conexao.getConnection();
            stat = con.createStatement();
            rs = stat.executeQuery("SELECT * FROM " + tabelaOrigem);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();

            while (rs.next()) {
                if (rs.getString(1).equals(id)) {
                    idTabela.add(rs.getString(2));
                }

            }

            for (int i = 0; i < idTabela.size(); i++) {
                resultSet.add(SelectByID(idTabela.get(i), tabelaDestino));
            }

        } catch (Exception e) {
            out.println(e.getMessage());

        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
            try {
                stat.close();
            } catch (Exception e) {
            }
        }

        return resultSet;

    }

    public Map<String, String> SelectKeyValue(String campo, String tabela) {
        Map<String, String> resultSet = new HashMap<String, String>();
        Validators vld = new Validators();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            con = Conexao.getConnection();
            stat = con.createStatement();
            rs = stat.executeQuery("SELECT * FROM " + tabela);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();

            while (rs.next()) {

                for (int i = 1; i < rsmd.getColumnCount(); i++) {
                    if (rsmd.getColumnName(i).equals(campo)) {
                        if (vld.isValidDate(rs.getString(i))) {
                            sdf.applyPattern("yyyy-MM-dd"); // voltando ao padrão para converter para Date
                            Date dt;
                            dt = sdf.parse(rs.getString(i));
                            sdf.applyPattern("dd/MM/yyyy"); // Convertendo para mostrar na View
                            resultSet.put(rs.getString(1), sdf.format(dt));
                        } else {
                            resultSet.put(rs.getString(1), rs.getString(i));
                        }
                    }
                }

            }

        } catch (Exception e) {
            out.println(e.getMessage());

        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
            try {
                stat.close();
            } catch (Exception e) {
            }
        }

        return resultSet;

    }

    public Map<String, String> SelectOnlyField(String tabela, String campo, String id) {
        Map<String, String> resultSet = new HashMap<String, String>();
        try {
            con = Conexao.getConnection();
            stat = con.createStatement();
            rs = stat.executeQuery("SELECT * FROM " + tabela);
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();

            while (rs.next()) {
                if (rs.getString(1).equals(id)) {
                    for (int i = 1; i < rsmd.getColumnCount(); i++) {

                        if (i == 1) {
                            resultSet.put(rsmd.getColumnName(i), rs.getString(i));
                        }

                        if (rsmd.getColumnName(i).equals(campo)) {
                            resultSet.put(campo, rs.getString(i));
                        }
                    }
                }

            }

        } catch (Exception e) {
            out.println(e.getMessage());

        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
            try {
                stat.close();
            } catch (Exception e) {
            }
        }

        return resultSet;
    }

    public Map<String, String> SelectLikePessoa(String nmCampo, String tabela) {
        Map<String, String> resultSet = new HashMap<String, String>();
        Validators vld = new Validators();
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        try {
            con = Conexao.getConnection();
            stat = con.createStatement();
            rs = stat.executeQuery("SELECT id_" + tabela + ", nm_pessoa FROM " + tabela + " AS s "
                    + " INNER JOIN pessoa AS p ON s.id_pessoa = p.id_pessoa WHERE nm_pessoa LIKE '%" + nmCampo + "%'");
            ResultSetMetaData rsmd = rs.getMetaData();
            int columnCount = rsmd.getColumnCount();
            while (rs.next()) {
                resultSet.put(rsmd.getColumnName(1), rs.getString(1));
                resultSet.put(rsmd.getColumnName(2), rs.getString(2));
            }
        } catch (Exception e) {
            out.println(e.getMessage());

        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
            try {
                stat.close();
            } catch (Exception e) {
            }
        }

        return resultSet;
    }

    // QUERIES DE ATUALIZAÇÃO
    // ******************************************************************************************************
    public String UpdateData(String tabela, LinkedHashMap<String, String> tableData) {
        try {
            con = Conexao.getConnection();
            stat = con.createStatement();

            Object[] arrayNmCampo = tableData.keySet().toArray();
            Object[] arrayValores = tableData.values().toArray();
            String query = "UPDATE " + tabela + " SET ";

            for (int i = 1; i < tableData.size(); i++) {
                if (i > 1) {
                    query += ",";
                }
                query += String.valueOf(arrayNmCampo[i]) + " = " + String.valueOf(arrayValores[i]);
            }

            query += " WHERE " + String.valueOf(arrayNmCampo[0]) + " = " + String.valueOf(arrayValores[0]);

            PreparedStatement ps = con.prepareStatement(query);

            ps.execute();
            ps.close();

            tableData.clear();

        } catch (Exception e) {
            return ("Error");

        } finally {

            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
            try {
                stat.close();
            } catch (Exception e) {
            }
        }
        //tableData.put("Success", "True");
        return ("Success");
    }

    // QUERIES DE EXCLUSÃO
    // ******************************************************************************************************
    public String RemoveData(String tabela, String nmCampoId, String id) {
        try {
            con = Conexao.getConnection();
            PreparedStatement ps = con.prepareStatement("DELETE FROM " + tabela + " WHERE " + nmCampoId + "=" + id);
            ps.execute();
            ps.close();
        } catch (Exception e) {
            return ("Error");
        } finally {
            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }

        }

        return "Success";
    }

    // QUERIES DE INCLUSÃO
    // ******************************************************************************************************
    public String InsertData(String tabela, LinkedHashMap<String, String> tableData) {
        try {
            con = Conexao.getConnection();
            stat = con.createStatement();

            Object[] arrayNmCampo = tableData.keySet().toArray();
            Object[] arrayValores = tableData.values().toArray();
            String query = "INSERT INTO " + tabela + " (";

            for (int i = 0; i < tableData.size(); i++) {
                if (i > 0) {
                    query += ",";
                }
                query += " " + String.valueOf(arrayNmCampo[i]);
            }

            query += ") VALUES (";

            for (int i = 0; i < tableData.size(); i++) {
                if (i > 0) {
                    query += ", ";
                }
                query += " " + String.valueOf(arrayValores[i]);
            }
            query += ")";

            stat.executeUpdate(query);

        } catch (Exception e) {
            return ("Error");

        } finally {

            try {
                rs.close();
            } catch (Exception e) {
            }
            try {
                con.close();
            } catch (Exception e) {
            }
            try {
                stat.close();
            } catch (Exception e) {
            }
        }
        //tableData.put("Success", "True");
        return ("Success");
    }
}
